USE ConsultTracking
GO
------------------------------------------------------------------
DROP TABLE UserDoStuff;
DROP TABLE LocationPurpose;
DROP TABLE ContactPurpose;
DROP TABLE UserJob;
DROP TABLE Contact;
DROP TABLE Note;
DROP TABLE [User];
DROP TABLE DoStuff;

DROP TABLE InvoiceTask;
DROP TABLE InvoicePO;
DROP TABLE Invoice;
DROP TABLE PurchaseOrder;
DROP TABLE Task;
DROP TABLE Job;
DROP TABLE BillingMethod;

DROP TABLE Location;
DROP TABLE PurposeType;
DROP TABLE Customer;
----------------------------------------------------------------------

-------------------
--CREATE Statements
-------------------

CREATE TABLE dbo.Contact
(
	[ContactID] int IDENTITY(1, 1),
	[FirstName] nvarchar(50) NOT NULL,
	[LastName] nvarchar(50) NOT NULL, 
	[OfficePhone]  nvarchar(50) NOT NULL DEFAULT '-',
	[MobilePhone] nvarchar(50) NOT NULL DEFAULT '-',
	[Email] nvarchar(100) NOT NULL DEFAULT '-',
	[WorkLocationID] int NOT NULL,
	[CustomerID] int NOT NULL,
	[Created] smalldatetime NOT NULL DEFAULT GETDATE(),
	[Modified] smalldatetime NOT NULL DEFAULT GETDATE(),
	CONSTRAINT Contact_PK PRIMARY KEY(ContactID)
);

CREATE TABLE dbo.ContactPurpose
(
	[ContactPurposeID] int IDENTITY(1, 1),
	[ContactID] int NOT NULL,
	[PurposeTypeID] int NOT NULL,
	[Description] nvarchar(300) NOT NULL,
	[Created] smalldatetime NOT NULL DEFAULT GETDATE(),
	[Modified] smalldatetime NOT NULL DEFAULT GETDATE(),
	CONSTRAINT ContactPurpose_UNIQUE UNIQUE(ContactID,PurposeTypeID),
	CONSTRAINT ContactPurpose_PK PRIMARY KEY(ContactPurposeID),
);

CREATE TABLE dbo.LocationPurpose
(
	[LocationPurposeID] int IDENTITY(1, 1),
	[LocationID] int NOT NULL,
	[PurposeTypeID] int NOT NULL,
	[Created] smalldatetime NOT NULL DEFAULT GETDATE(),
	[Modified] smalldatetime NOT NULL DEFAULT GETDATE(),
	CONSTRAINT LocationPurpose_UNIQUE UNIQUE(LocationID,PurposeTypeID),
	CONSTRAINT LocationPurpose_PK PRIMARY KEY(LocationPurposeID)
);

CREATE TABLE dbo.Customer
(
	[CustomerID] int IDENTITY(1, 1),
	[CustomerName] nvarchar(100) NOT NULL UNIQUE,
	[Phone] nvarchar(50) NOT NULL default '-',
	[Fax] nvarchar(50) NOT NULL default '-',
	[Active] bit NOT NULL DEFAULT 1,
	[Created] smalldatetime NOT NULL DEFAULT GETDATE(),
	[Modified] smalldatetime NOT NULL DEFAULT GETDATE(),
	CONSTRAINT Customer_PK PRIMARY KEY(CustomerID)
);

CREATE TABLE dbo.Location
(
	[LocationID] int IDENTITY(1, 1),
	[CustomerID] int NOT NULL,
	[LocationName] nvarchar(50) NOT NULL,
	[Address1]  nvarchar(60) NOT NULL DEFAULT '-',
	[Address2] nvarchar(60) NOT NULL DEFAULT '-',
	[City] nvarchar(100) NOT NULL,
	[State] nvarchar(30) NOT NULL,
	[Zip] nvarchar(10) NOT NULL,
	[Created] smalldatetime NOT NULL DEFAULT GETDATE(),
	[Modified] smalldatetime NOT NULL DEFAULT GETDATE(),
	CONSTRAINT Location_PK PRIMARY KEY(LocationID)
);
--Leo Added Description
CREATE TABLE dbo.PurposeType
(
	[PurposeTypeID] int IDENTITY(1, 1),
	[Name] nvarchar(50) NOT NULL,
	[Description] nvarchar(300) NOT NULL,
	[Created] smalldatetime NOT NULL DEFAULT GETDATE(),
	[Modified] smalldatetime NOT NULL DEFAULT GETDATE(),
	CONSTRAINT PurposeType_PK PRIMARY KEY(PurposeTypeID)
);

CREATE TABLE dbo.[User]
(
	[UserID] int IDENTITY(1, 1),
	[LogOnName] nvarchar(15) NOT NULL UNIQUE,
	[CustomerID] int NULL,
	[Active] bit NOT NULL DEFAULT 1,
	[Created] smalldatetime NOT NULL DEFAULT GETDATE(),
	[Modified] smalldatetime NOT NULL DEFAULT GETDATE(),
	CONSTRAINT User_PK PRIMARY KEY(UserID)
);

CREATE TABLE dbo.UserJob
(
	[UserJobID] int IDENTITY(1,1),
	[JobID] int NOT NULL,
	[UserID] int NOT NULL,
	[Created] smalldatetime NOT NULL DEFAULT GETDATE(),
	[Modified] smalldatetime NOT NULL DEFAULT GETDATE(),
	CONSTRAINT UserJob_PK PRIMARY KEY(UserJobID)
);

CREATE TABLE dbo.DoStuff
(
	[DoStuffID] int IDENTITY(1, 1),
	[DoStuffName] nvarchar (100) NOT NULL,
	[DoStuffDescription] nvarchar (300) NOT NULL,
	[Created] smalldatetime NOT NULL DEFAULT GETDATE(),
	[Modified] smalldatetime NOT NULL DEFAULT GETDATE(),
	CONSTRAINT DoStuff_PK PRIMARY KEY(DoStuffID)
);

CREATE TABLE dbo.UserDoStuff
(
	[UserDoStuffID] int IDENTITY(1, 1),
	[DoStuffID] int NOT NULL,
	[UserID] int NOT NULL,
	[Doable] bit NOT NULL DEFAULT 0,
	[Created] smalldatetime NOT NULL DEFAULT GETDATE(),
	[Modified] smalldatetime NOT NULL DEFAULT GETDATE(),
	CONSTRAINT UserDoStuff_UNIQUE UNIQUE (DoStuffID,UserID),
	CONSTRAINT UserDoStuffID_PK PRIMARY KEY(UserDoStuffID)
);

CREATE TABLE dbo.BillingMethod
(
	[BillingMethodID] int IDENTITY(1,1),
	[Description] nvarchar(100) NOT NULL,
	[Created] smalldatetime NOT NULL DEFAULT GETDATE(),
	[Modified] smalldatetime NOT NULL DEFAULT GETDATE(),
	CONSTRAINT BillingMethod_PK PRIMARY KEY(BillingMethodID)
);

CREATE TABLE dbo.Job
(
	[JobID] int IDENTITY(1, 1),
	[CustomerID] int NOT NULL,
	[Description] nvarchar(100) NOT NULL,
	[BillingMethodID] int NOT NULL,
	[Finalized] bit NOT NULL DEFAULT 0,
	[DueDate] smalldatetime NULL,
	[Created] smalldatetime NOT NULL DEFAULT GETDATE(),
	[Modified] smalldatetime NOT NULL DEFAULT GETDATE(),
	CONSTRAINT Job_PK PRIMARY KEY(JobID)
);

create table dbo.Task
(
	[TaskID] int IDENTITY(1,1),
	[JobID] int NOT NULL,
	[LocationID] int NOT NULL,
	[Description] nvarchar(100) NOT NULL,
	[StartTime] smalldatetime NULL ,
	[EndTime] smalldatetime NULL ,
	[Addressed] bit NOT NULL DEFAULT 0,
	[Status] nvarchar(100) NOT NULL DEFAULT '',
	[Created] smalldatetime NOT NULL DEFAULT GETDATE(),
	[Modified] smalldatetime NOT NULL DEFAULT GETDATE(),
	CONSTRAINT Task_PK PRIMARY KEY(TaskID)
);


CREATE TABLE dbo.PurchaseOrder
(
	[PurchaseOrderID] int IDENTITY(1,1),
	[TaskID] int NOT NULL,
	[UserID] int NOT NULL,
	[Description] nvarchar(100) NOT NULL,
	[Manufacturer] nvarchar(50) NOT NULL,
	[ModelNumber] nvarchar(50) NOT NULL,
	[Quantity] int NOT NULL DEFAULT 1,
	[Price] decimal(8,2) NOT NULL DEFAULT 0,
	[Created] smalldatetime NOT NULL DEFAULT GETDATE(),
	[Modified] smalldatetime NOT NULL DEFAULT GETDATE(),
	CONSTRAINT PurchaseOrder_PK PRIMARY KEY(PurchaseOrderID)
);

CREATE TABLE dbo.Note
(
	[NoteID] int IDENTITY(1,1),
	[TaskID] int NOT NULL,
	[UserID] int NOT NULL,
	[Description] nvarchar(1000) NOT NULL,
	[Created] smalldatetime NOT NULL DEFAULT GETDATE(),
	[Modified] smalldatetime NOT NULL DEFAULT GETDATE(),
	CONSTRAINT Note_PK PRIMARY KEY(NoteID)
);

CREATE TABLE dbo.Invoice
(
	[InvoiceID] int IDENTITY(1,1),
	[JobID] int UNIQUE NOT NULL,
	[Description] nvarchar(100) NOT NULL,
	[Paid] BIT NOT NULL DEFAULT 0,
	[Created] smalldatetime NOT NULL DEFAULT GETDATE(),
	[Modified] smalldatetime NOT NULL DEFAULT GETDATE(),
	CONSTRAINT Invoice_PK PRIMARY KEY(InvoiceID)
);

CREATE TABLE dbo.InvoiceTask
(
	[InvoiceTaskID] int IDENTITY(1,1),
	[InvoiceID] int NOT NULL,
	[Description] nvarchar(100) NOT NULL,
	[TimeBilled] decimal(5, 2) NOT NULL,
	[Created] smalldatetime NOT NULL DEFAULT GETDATE(),
	[Modified] smalldatetime NOT NULL DEFAULT GETDATE(),
	CONSTRAINT InvoiceTask_PK PRIMARY KEY(InvoiceTaskID)
);

CREATE TABLE dbo.InvoicePO
(
	[InvoicePoID] int IDENTITY(1,1),
	[InvoiceID] int NOT NULL,
	[Description] nvarchar(100) NOT NULL,
	[Price] decimal(8,2) NOT NULL DEFAULT 0,
	[Created] smalldatetime NOT NULL DEFAULT GETDATE(),
	[Modified] smalldatetime NOT NULL DEFAULT GETDATE(),
	CONSTRAINT InvoicePO_PK PRIMARY KEY(InvoicePoID)
);
-------------------------
--FOREIGN KEY Constraints
-------------------------
ALTER TABLE dbo.Contact
	ADD CONSTRAINT ContactCustomer_FK FOREIGN KEY(CustomerID)
		REFERENCES Customer(CustomerID)

ALTER TABLE dbo.Contact
	ADD CONSTRAINT ContactLocation_FK FOREIGN KEY(WorkLocationID)
		REFERENCES Location(LocationID)

ALTER TABLE dbo.ContactPurpose
	ADD CONSTRAINT ContactPurposeCustomer_FK FOREIGN KEY(ContactID)
		REFERENCES Contact(ContactID)

ALTER TABLE dbo.ContactPurpose
	ADD CONSTRAINT ContactPurposePurposeType_FK FOREIGN KEY(PurposeTypeID)
		REFERENCES PurposeType(PurposeTypeID)

ALTER TABLE dbo.LocationPurpose
	ADD CONSTRAINT LocationPurposeLocation_FK FOREIGN KEY(LocationID)
		REFERENCES Location(LocationID)

ALTER TABLE dbo.LocationPurpose
	ADD CONSTRAINT LocationPurposePurposeType_FK FOREIGN KEY(PurposeTypeID)
		REFERENCES PurposeType(PurposeTypeID)

--Leo: Added Constraint LocationCustomer_FK
ALTER TABLE dbo.Location
	ADD CONSTRAINT LocationCustomer_FK FOREIGN KEY(CustomerID)
		REFERENCES Customer(CustomerID)

ALTER TABLE dbo.[User]
	ADD CONSTRAINT UserCustomer_FK FOREIGN KEY (CustomerID)
		REFERENCES Customer(CustomerID)

ALTER TABLE dbo.UserJob
	ADD CONSTRAINT UserJobUser_FK FOREIGN KEY (UserID)
		REFERENCES [User](UserID)

ALTER TABLE dbo.UserJob
	ADD CONSTRAINT UserJobJob_FK FOREIGN KEY (JobID)
		REFERENCES Job(JobID)

ALTER TABLE dbo.UserDoStuff
	ADD CONSTRAINT UserDoStuffDoStuff_FK FOREIGN KEY (DoStuffID)
		REFERENCES DoStuff(DoStuffID)

ALTER TABLE dbo.UserDoStuff
	ADD CONSTRAINT UserDoStuffUser_FK FOREIGN KEY (UserID)
		REFERENCES [User](UserID)

ALTER TABLE dbo.Job
	ADD CONSTRAINT JobBillingMethod_FK FOREIGN KEY(BillingMethodID)
		REFERENCES BillingMethod(BillingMethodID)

ALTER TABLE dbo.Job
	ADD CONSTRAINT JobCustomer_FK FOREIGN KEY (CustomerID)
		REFERENCES Customer(CustomerID)

ALTER TABLE dbo.Task
	ADD CONSTRAINT TaskJob_FK FOREIGN KEY(JobID)
		REFERENCES Job(JobID)

ALTER TABLE dbo.Task
	ADD CONSTRAINT TaskLocation_FK FOREIGN KEY(LocationID)
		REFERENCES Location(LocationID)

ALTER TABLE dbo.PurchaseOrder
	ADD CONSTRAINT PurchaseOrderTask_FK FOREIGN KEY(TaskID)
		REFERENCES Task(TaskID)

ALTER TABLE dbo.Note
	ADD CONSTRAINT NoteTask_FK FOREIGN KEY(TaskID)
		REFERENCES Task(TaskID)

ALTER TABLE dbo.Note
	ADD CONSTRAINT NoteUser_FK FOREIGN KEY(UserID)
		REFERENCES [User](UserID)

ALTER TABLE dbo.Invoice
	ADD CONSTRAINT InvoiceJob_FK FOREIGN KEY(JobID)
		REFERENCES Job(JobID)

ALTER TABLE dbo.InvoiceTask
	ADD CONSTRAINT InvoiceTaskIvoice_FK FOREIGN KEY(InvoiceID)
		REFERENCES Invoice(InvoiceID)

ALTER TABLE dbo.InvoicePO
	ADD CONSTRAINT InvoicePoInvoice_FK FOREIGN KEY(InvoiceID)
		REFERENCES Invoice(InvoiceID)